﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Google.GData.Spreadsheets;
using Google.GData.Client;

namespace Copycat
{
    public static class DatabaseManager
    {
      private static  SpreadsheetsService service;
        private static SpreadsheetFeed feed;
        public static bool BindData(ref SpreadsheetsService  Refservice,ref SpreadsheetFeed Reffeed)
        {
            service = (SpreadsheetsService)HttpContext.Current.Session["Service"];
            feed = (SpreadsheetFeed)HttpContext.Current.Session["Feed"];



            Refservice = service;
            Reffeed = feed;

            if (feed != null)
                return true;

            service = new SpreadsheetsService("NewsletterService");

            service.Credentials = new GDataCredentials("jobs.copycat@gmail.com", "copycat_2012");

            SpreadsheetQuery query = new SpreadsheetQuery();

            feed = service.Query(query);

            if (feed.Entries.Count == 0)
                return false;

            HttpContext.Current.Session["Service"] = service;
            HttpContext.Current.Session["Feed"] = feed;

            Refservice = service;
            Reffeed = feed;
            return true;
        }
        public static bool BindData()
        {
            service = (SpreadsheetsService)HttpContext.Current.Session["Service"];
            feed = (SpreadsheetFeed)HttpContext.Current.Session["Feed"];
           
            if (feed != null)
                return true;

            service = new SpreadsheetsService("NewsletterService");

            service.Credentials = new GDataCredentials("jobs.copycat@gmail.com", "copycat_2012");

            SpreadsheetQuery query = new SpreadsheetQuery();

            feed = service.Query(query);

            if (feed.Entries.Count == 0)
                return false;

            HttpContext.Current.Session["Service"] = service;
            HttpContext.Current.Session["Feed"] = feed;
            return true;
        }



        public static bool SendComplaint(string Name, string Tel, string Subject, string Mail, string Msg)
        {
            if (!BindData())
                return false;

            try
            {
                SpreadsheetEntry spreadsheet = new SpreadsheetEntry();
                foreach (SpreadsheetEntry e in feed.Entries)
                    if (e.Title.Text == "complaints")
                        spreadsheet = e;

                WorksheetFeed wsFeed = spreadsheet.Worksheets;
                WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];

                // Define the URL to request the list feed of the worksheet.
                AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);

                // Fetch the list feed of the worksheet.
                ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
                ListFeed listFeed = service.Query(listQuery);

                // Create a local representation of the new row.
                ListEntry row = new ListEntry();
                row.Elements.Add(new ListEntry.Custom() { LocalName = "sender", Value = Name });
                row.Elements.Add(new ListEntry.Custom() { LocalName = "email", Value = Mail });
                row.Elements.Add(new ListEntry.Custom() { LocalName = "message", Value = Msg });
                row.Elements.Add(new ListEntry.Custom() { LocalName = "tel", Value = Tel });
                row.Elements.Add(new ListEntry.Custom() { LocalName = "subject", Value = Subject });
                row.Elements.Add(new ListEntry.Custom() { LocalName = "status", Value = "0" });


                // Send the new row to the API for insertion.
                service.Insert(listFeed, row);

                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool SetComplaintStatus(string ComplaintID, string Status)
        {
            if (!BindData())
                return false;

            try
            {
                SpreadsheetEntry spreadsheet = new SpreadsheetEntry();

                foreach (SpreadsheetEntry en in feed.Entries)
                    if (en.Title.Text == "complaints")
                    {
                        spreadsheet = en;
                        break;
                    }

                WorksheetFeed wsFeed = spreadsheet.Worksheets;
                WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];

                // Define the URL to request the list feed of the worksheet.
                AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);

                ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
                listQuery.SpreadsheetQuery = "tel = " + ComplaintID;
                ListFeed listFeed = service.Query(listQuery);

                ListEntry entry = (ListEntry)listFeed.Entries[0];
                entry.Elements[5] = new ListEntry.Custom() { LocalName = "status", Value = "1" };
                entry.Update();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static int Login(string UserName, string Password)
        {
            if (!BindData())
                return 0;

            
            SpreadsheetEntry spreadsheet = new SpreadsheetEntry();
            foreach (SpreadsheetEntry en in feed.Entries)
                if (en.Title.Text == "users")
                {
                    spreadsheet = en;
                    break;
                }

            WorksheetFeed wsFeed = spreadsheet.Worksheets;
            WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];

            // Define the URL to request the list feed of the worksheet.
            AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);

            ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
            listQuery.SpreadsheetQuery = string.Format("user = {0} and password={1}", UserName, Password);
            ListFeed listFeed = service.Query(listQuery);
            if (listFeed.Entries.Count == 0)
                return 1;
            else return 2;
        }

        public static bool DeleteRecord(string sheetName, string criteria, string value)
        {
            if (!BindData())
                return false;

            try
            {
                SpreadsheetEntry spreadsheet = new SpreadsheetEntry();

                foreach (SpreadsheetEntry en in feed.Entries)
                    if (en.Title.Text == sheetName)
                    {
                        spreadsheet = en;
                        break;
                    }

                WorksheetFeed wsFeed = spreadsheet.Worksheets;
                WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];

                // Define the URL to request the list feed of the worksheet.
                AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);

                ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
                listQuery.SpreadsheetQuery =criteria+ " = " + value;
                ListFeed listFeed = service.Query(listQuery);

                listFeed.Entries[0].Delete();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool Subscribe(string Name, string Mail)
        {
            if (!BindData())
                return false;
           
            try
                
            {
                SpreadsheetEntry spreadsheet = new SpreadsheetEntry();
                foreach (SpreadsheetEntry en in feed.Entries)
                    if (en.Title.Text == "emails")
                    {
                        spreadsheet = en;
                        break;
                    }
                
            //Console.WriteLine(spreadsheet.Title.Text);

            // Get the first worksheet of the first spreadsheet.
            // TODO: Choose a worksheet more intelligently based on your
            // app's needs.
            WorksheetFeed wsFeed = spreadsheet.Worksheets;
            WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];

            // Define the URL to request the list feed of the worksheet.
            AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);

            // Fetch the list feed of the worksheet.
            ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
            ListFeed listFeed = service.Query(listQuery);

            // Create a local representation of the new row.
            ListEntry row = new ListEntry();
            row.Elements.Add(new ListEntry.Custom() { LocalName = "name", Value = Name });
            row.Elements.Add(new ListEntry.Custom() { LocalName = "mail", Value = Mail });


            // Send the new row to the API for insertion.
            service.Insert(listFeed, row);
            return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool RequestDiscountCard(string Name, string Mail,string Tel, string FacebookName)
        {

            if (!BindData())
                return false;

            try
            {
                SpreadsheetEntry spreadsheet = new SpreadsheetEntry();
                foreach (SpreadsheetEntry e in feed.Entries)
                    if (e.Title.Text == "discountreq")
                        spreadsheet = e;

                WorksheetFeed wsFeed = spreadsheet.Worksheets;
                WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];

                // Define the URL to request the list feed of the worksheet.
                AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);

                // Fetch the list feed of the worksheet.
                ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
                ListFeed listFeed = service.Query(listQuery);

                // Create a local representation of the new row.
                ListEntry row = new ListEntry();
                row.Elements.Add(new ListEntry.Custom() { LocalName = "name", Value = Name });
                row.Elements.Add(new ListEntry.Custom() { LocalName = "tel", Value = Tel });
                row.Elements.Add(new ListEntry.Custom() { LocalName = "email", Value = Mail });
                row.Elements.Add(new ListEntry.Custom() { LocalName = "facebookname", Value = FacebookName });


                // Send the new row to the API for insertion.
                service.Insert(listFeed, row);

                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool AddDiscountID(string Name, string Tel, string faceName, string Mail, string ID)
        {
            if (!BindData())
                return false;

            try
            {
                SpreadsheetEntry spreadsheet = new SpreadsheetEntry();
                foreach (SpreadsheetEntry e in feed.Entries)
                    if (e.Title.Text == "discountids")
                        spreadsheet = e;

                WorksheetFeed wsFeed = spreadsheet.Worksheets;
                WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];

                // Define the URL to request the list feed of the worksheet.
                AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);

                // Fetch the list feed of the worksheet.
                ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
                ListFeed listFeed = service.Query(listQuery);

                // Create a local representation of the new row.
                ListEntry row = new ListEntry();
                row.Elements.Add(new ListEntry.Custom() { LocalName = "name", Value = Name });
                row.Elements.Add(new ListEntry.Custom() { LocalName = "tel", Value = Tel });
                row.Elements.Add(new ListEntry.Custom() { LocalName = "email", Value = Mail });
                row.Elements.Add(new ListEntry.Custom() { LocalName = "facebookname", Value = faceName });
                row.Elements.Add(new ListEntry.Custom() { LocalName = "id", Value = ID });


                // Send the new row to the API for insertion.
                service.Insert(listFeed, row);

                return true;
            }
            catch
            {
                return false;
            }
        }

        public static AtomEntryCollection RetrieveRows(string sheetName)
        {
            if (!BindData())
                return null;

            SpreadsheetEntry spreadsheet = new SpreadsheetEntry();
            foreach (SpreadsheetEntry en in feed.Entries)
                if (en.Title.Text == sheetName)
                {
                    spreadsheet = en;
                    break;
                }

            WorksheetFeed wsFeed = spreadsheet.Worksheets;
            WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];

            // Define the URL to request the list feed of the worksheet.
            AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);

            // Fetch the list feed of the worksheet.
            ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
            ListFeed listFeed = service.Query(listQuery);

            return listFeed.Entries;
        }
    }
}